#!/usr/bin/python
 #-*-coding: utf-8 -*- 
# script qui créer la base de données iris à partir du fichier de données d'iris
# Roxane Levy
import MySQLdb 

# test variables for database access
HOST = 'localhost'
USER = 'root'
PASSWD = 'password'
DATABASE = 'ADPD_Database'


def prettyPrint(data):
    print "There are %d data items"%len(data)
    for i,d in enumerate(data):
        print "%d --- "%i, d

# make a connection to the DATABASE database
db_connection = MySQLdb.connect(
        host=HOST,
        user=USER, 
        passwd=PASSWD, 
        #db=DATABASE
        )

# we'll need a cursor to this database to execute commands
cursor = db_connection.cursor()

# demonstrate the creation of a database
#cursor.execute('show databases')
#prettyPrint(cursor.fetchall())
cursor.execute("DROP DATABASE IF EXISTS ADPD_Database")
cursor.execute('create database ADPD_Database')


## switch to using the newly-created database
cursor.execute('use ADPD_Database')


# use the cursor to execute a 'create table' command
sql = """
CREATE TABLE Iris(
  id INTEGER  NOT NULL AUTO_INCREMENT,
  color1 DOUBLE NOT NULL,
  color2 DOUBLE NOT NULL,
  color3 DOUBLE NOT NULL,
  color4 DOUBLE NOT NULL,
  espece VARCHAR(30) NOT NULL,
  PRIMARY KEY(id) )
"""
cursor.execute(sql)


# Prepare SQL query to INSERT a record into the database.


with open("iris.csv", "r") as iris :
  lines = iris.readlines()


for line in lines :
  string = line.split()
  data = string[0].split(',')
  sql = """INSERT INTO Iris (color1,color2,color3,color4,espece) 
VALUES ('%s','%s','%s','%s','%s')"""%(float(data[0]),float(data[1]),float(data[2]), float(data[3]),str(data[4]))
  cursor.execute(sql)



try:
   cursor.execute('SELECT color1 FROM Iris')
   db_connection.commit()
except:
   # Rollback in case there is any error
   db_connection.rollback()


db_connection.close()

